#!/bin/sh
#
# Copyright (C) 2000-2022 Kern Sibbald
# License: BSD 2-Clause; see file LICENSE-FOSS
#
# Shell script to update MySQL
#
echo " "
echo "This script will update a Bacula MySQL database from version 1025 to 1026"
echo " "
bindir=@MYSQL_BINDIR@
PATH="$bindir:$PATH"
db_name=${db_name:-@db_name@}

mysql $* -D ${db_name} -e "select VersionId from Version\G" >/tmp/$$
DBVERSION=`sed -n -e 's/^VersionId: \(.*\)$/\1/p' /tmp/$$`
if [ $DBVERSION != 1025 ] ; then
   echo " "
   echo "The existing database is version $DBVERSION !!"
   echo "This script can only update an existing version 1025 database to version 1026."
   echo "Error. Cannot upgrade this database."
   echo " "
   exit 1
fi

if mysql $* -f  <<END-OF-DATA
USE ${db_name};
CREATE TABLE MalwareMD5
(
    MD5     char(22)       -- Same as in File
);
CREATE INDEX malwaremd5_idx on MalwareMD5 (MD5);

CREATE TABLE MalwareSHA256
(
    MD5     char(65)       -- Same as in File
);
CREATE INDEX malwaresha256_idx on MalwareSHA256 (MD5);

CREATE TABLE FileEvents 
(
   Id   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, -- Used for replication
   Time DATETIME DEFAULT NOW(),
   SourceJobId   int,        -- Can be the Verify job id for example, or the jobid during a restore/backup
   JobId         int,        -- JobId where the file was found. Used for pruning
   FileIndex     int,        -- File reference
   Type          char,       -- Event type (antivirus, malware scanning (M), lost file)
   Description   blob,       -- Description of the event
   Severity      int,        -- level of severity. (0 OK, 100 Important)
   Source        blob,       -- Information about the source of the event
   PRIMARY KEY(Id)
);

CREATE INDEX FileEvents_jobid_idx ON FileEvents (JobId, FileIndex);
CREATE INDEX FileEvents_sourcejobid_idx ON FileEvents (SourceJobId);


DROP INDEX meta_emailsubjectbody ON MetaEmail;
DROP INDEX meta_emailowner ON MetaEmail;
DROP INDEX meta_emailconversationid on MetaEmail;

ALTER TABLE MetaEmail
    MODIFY COLUMN EmailTenant TEXT,
    MODIFY COLUMN EmailOwner TEXT,
    MODIFY COLUMN EmailId TEXT,
    MODIFY COLUMN EmailFrom TEXT,
    MODIFY COLUMN EmailInternetMessageId TEXT,
    MODIFY COLUMN EmailConversationId TEXT,
    MODIFY COLUMN Plugin TEXT;

CREATE FULLTEXT INDEX meta_emailsubjectbody ON MetaEmail 
       (EmailSubject, EmailBodyPreview, EmailTo, EmailCc, EmailFrom);
CREATE INDEX meta_emailowner ON MetaEmail (EmailTenant(255), EmailOwner(255));
CREATE INDEX meta_emailconversationid on MetaEmail (EmailConversationId(255));
CREATE INDEX meta_emailid on MetaEmail (EmailId(255));

DROP INDEX meta_attachmentowner ON MetaAttachment;
DROP INDEX meta_attachmentemailid ON MetaAttachment;

ALTER TABLE MetaAttachment
    MODIFY COLUMN AttachmentTenant TEXT,
    MODIFY COLUMN AttachmentOwner  TEXT,
    MODIFY COLUMN AttachmentEmailId TEXT,
    MODIFY COLUMN Plugin           TEXT;

CREATE INDEX meta_attachmentowner ON MetaAttachment (AttachmentTenant(255),AttachmentOwner(255));
CREATE INDEX meta_attachmentemailid ON MetaAttachment (AttachmentEmailId(255));

ALTER TABLE Media
    ADD COLUMN Protected    tinyint default 0,
    ADD COLUMN UseProtect   tinyint default 0,
    ADD COLUMN VolEncrypted tinyint default 0;

ALTER TABLE Object
    ADD COLUMN FileIndex integer not null default 0,
    MODIFY COLUMN ObjectSource BLOB,
    MODIFY COLUMN ObjectName BLOB,
    MODIFY COLUMN ObjectType BLOB,
    MODIFY COLUMN ObjectCategory BLOB,
    MODIFY COLUMN ObjectUUID BLOB;

ALTER TABLE FileSet
    ADD COLUMN Content BLOB;

ALTER TABLE Job
    ADD COLUMN RealStartTime DATETIME,
    ADD COLUMN isVirtualFull     TINYINT    default 0,
    ADD COLUMN CompressRatio     float      default 0,
    ADD COLUMN Rate              float      default 0,
    ADD COLUMN LastReadStorageId Integer    default 0,
    ADD COLUMN LastReadDevice    blob,
    ADD COLUMN WriteStorageId    integer    default 0,
    ADD COLUMN WriteDevice       blob,
    ADD COLUMN StatusInfo        blob,
    ADD COLUMN Encrypted         int        default 0;

ALTER TABLE JobHisto
    ADD COLUMN RealStartTime DATETIME,
    ADD COLUMN isVirtualFull     TINYINT    default 0,
    ADD COLUMN CompressRatio     float      default 0,
    ADD COLUMN Rate              float      default 0,
    ADD COLUMN LastReadStorageId Integer    default 0,
    ADD COLUMN LastReadDevice    blob,
    ADD COLUMN WriteStorageId    integer    default 0,
    ADD COLUMN WriteDevice       blob,
    ADD COLUMN StatusInfo        blob,
    ADD COLUMN Encrypted         int        default 0;

INSERT INTO Events (EventsCode, EventsType, EventsTime, EventsDaemon, EventsSource, EventsRef, EventsText) VALUES
  ('DU0001', 'catalog_update', NOW(), '*SHELL*', 'update_bacula_tables', 'pid$$', 'Catalog schema was updated to 1026');
UPDATE Version SET VersionId=1026;

END-OF-DATA
then
   echo "Update of Bacula MySQL tables 1025 to 1026 succeeded."
   getVersion
else
   echo "Update of Bacula MySQL tables 1025 to 1026 failed."
   exit 1
fi

exit 0
